﻿namespace DbWorker.AccessModels
{
    using System.Collections.Generic;
    using System;
    using System.Data;
    using System.Data.SQLite;
    using DbWorker.DataModels;

    using NLog;

    /// <summary>
    /// Access model to the letters table.
    /// </summary>
    public class LetterAm
    {
        private readonly SQLiteConnection _connection;
        private readonly Logger _logger = LogManager.GetCurrentClassLogger();

        public LetterAm(SQLiteConnection connection)
        {
            _connection = connection;
        }

        /// <summary>
        /// Gets all letters.
        /// </summary>
        public bool SelectAll(out List<LetterDm> lettersDm)
        {
            lettersDm = new List<LetterDm>();
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("SELECT * FROM letter;", _connection);
                using (command)
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            lettersDm.Add(new LetterDm
                            {
                                Id = (long)reader["id"],
                                RuleId = (reader["folder_rule_id"] == DBNull.Value) ? -1 : (long)reader["folder_rule_id"],
                                Uid = (reader["uid"] == DBNull.Value) ? string.Empty : (string)reader["uid"],
                                Subject = (string)reader["subject"],
                                From = (string)reader["letter_from"],
                                To = (string)reader["letter_to"],
                                MessageText = (string)reader["message_text"],
                                LetterDate = (DateTime)reader["letter_date"],
                                Type = (LetterType)reader["type"]
                            });
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Gets letters for the current rule.
        /// </summary>
        public bool SelectByRule(long ruleId, out List<LetterDm> lettersDm)
        {
            lettersDm = new List<LetterDm>();
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("SELECT * FROM letter WHERE folder_rule_id = @i_folder_id;", _connection);
                using (command)
                {
                    command.Parameters.AddWithValue("@i_folder_id", ruleId);
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            lettersDm.Add(new LetterDm
                            {
                                Id = (long)reader["id"],
                                RuleId = ruleId,
                                Uid = (reader["uid"] == DBNull.Value) ? string.Empty : (string)reader["uid"],
                                Subject = (string)reader["subject"],
                                From = (string)reader["letter_from"],
                                To = (string)reader["letter_to"],
                                MessageText = (string)reader["message_text"],
                                LetterDate = (DateTime)reader["letter_date"],
                                Type = (LetterType)reader["type"]
                            });
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Gets letters for the current rule.
        /// </summary>
        public bool Search(string query, out List<LetterDm> lettersDm)
        {
            lettersDm = new List<LetterDm>();
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand(
                    "SELECT letter.id, letter.folder_rule_id, letter.uid, letter.subject, "
                    + "letter.letter_from, letter.letter_to, letter.message_text, "
                    + "letter.letter_date, letter.type FROM letter LEFT JOIN file_metadata "
                    + "ON letter.id = file_metadata.letter_id "
                    + "WHERE letter.letter_from LIKE @i_query OR letter.letter_to LIKE @i_query "
                    + "OR letter.subject LIKE @i_query OR letter.message_text LIKE @i_query "
                    + "OR file_metadata.file_name LIKE @i_query GROUP BY letter.id;", _connection);

                using (command)
                {
                    query = string.Format("%{0}%", query);
                    command.Parameters.AddWithValue("@i_query", query);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            lettersDm.Add(new LetterDm
                            {
                                Id = (long)reader["id"],
                                RuleId = (reader["folder_rule_id"] == DBNull.Value) ? -1 : (long)reader["folder_rule_id"],
                                Uid = (reader["uid"] == DBNull.Value) ? string.Empty : (string)reader["uid"],
                                Subject = (string)reader["subject"],
                                From = (string)reader["letter_from"],
                                To = (string)reader["letter_to"],
                                MessageText = (string)reader["message_text"],
                                LetterDate = (DateTime)reader["letter_date"],
                                Type = (LetterType)reader["type"]
                            });
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Adds a new letter to the database.
        /// </summary>
        public bool Insert(LetterDm letterDm, out long letterId)
        {
            letterId = -1;
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("INSERT INTO letter (folder_rule_id, uid, subject, "
                    + "letter_from, letter_to, message_text, letter_date, type) "
                    + "VALUES (@folder_rule_id, @uid, @subject, @letter_from, "
                    + "@letter_to, @message_text, @letter_date, @type); "
                    + "SELECT last_insert_rowid();", _connection);

                using (command)
                {
                    if (letterDm.RuleId <= 0)
                    {
                        command.Parameters.AddWithValue("@folder_rule_id", DBNull.Value);
                    }
                    else
                    {
                        command.Parameters.AddWithValue("@folder_rule_id", letterDm.RuleId);
                    }

                    command.Parameters.AddWithValue("@uid", letterDm.Uid);
                    command.Parameters.AddWithValue("@subject", letterDm.Subject);
                    command.Parameters.AddWithValue("@letter_from", letterDm.From);
                    command.Parameters.AddWithValue("@letter_to", letterDm.To);
                    command.Parameters.AddWithValue("@message_text", letterDm.MessageText);
                    command.Parameters.AddWithValue("@letter_date", letterDm.LetterDate);
                    command.Parameters.AddWithValue("@type", letterDm.Type);

                    letterId = (long)command.ExecuteScalar();
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Changes letter in database.
        /// </summary>
        public bool Update(LetterDm letter)
        {
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("UPDATE letter SET "
                        + "folder_rule_id = @folder_rule_id, uid = @uid, subject = @subject, letter_from = @letter_from, "
                        + "letter_to = @letter_to, message_text = @message_text, letter_date = @letter_date, type = @type "
                        + "WHERE id = @id;",
                        _connection);

                using (command)
                {
                    if (letter.RuleId <= 0)
                    {
                        command.Parameters.AddWithValue("@folder_rule_id", DBNull.Value);
                    }
                    else
                    {
                        command.Parameters.AddWithValue("@folder_rule_id", letter.RuleId);
                    }

                    command.Parameters.AddWithValue("@uid", letter.Uid);
                    command.Parameters.AddWithValue("@subject", letter.Subject);
                    command.Parameters.AddWithValue("@letter_from", letter.From);
                    command.Parameters.AddWithValue("@letter_to", letter.To);
                    command.Parameters.AddWithValue("@message_text", letter.MessageText);
                    command.Parameters.AddWithValue("@letter_date", letter.LetterDate);
                    command.Parameters.AddWithValue("@type", letter.Type);
                    command.Parameters.AddWithValue("@id", letter.Id);

                    command.ExecuteNonQuery();
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Deletes letter from the database.
        /// </summary>
        public bool Delete(long letterId)
        {
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("DELETE FROM letter WHERE id = @id;", _connection);
                using (command)
                {
                    command.Parameters.AddWithValue("@id", letterId);
                    command.ExecuteNonQuery();
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }
    }
}
